package com.gupao.jly521.apache.common;

/**
 * @author Jly
 * @date 2018/9/3  8:51
 */
import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.Arrays;

import java.util.List;

import java.util.Map;

import java.util.Properties;



import org.apache.commons.dbcp.BasicDataSource;

import org.apache.commons.dbcp.BasicDataSourceFactory;

import org.apache.commons.dbutils.DbUtils;

import org.apache.commons.dbutils.QueryRunner;

import org.apache.commons.dbutils.handlers.ArrayHandler;

import org.apache.commons.dbutils.handlers.ArrayListHandler;

import org.apache.commons.dbutils.handlers.BeanHandler;

import org.apache.commons.dbutils.handlers.BeanListHandler;

import org.apache.commons.dbutils.handlers.ColumnListHandler;

import org.apache.commons.dbutils.handlers.KeyedHandler;

import org.apache.commons.dbutils.handlers.MapHandler;

import org.apache.commons.dbutils.handlers.MapListHandler;

import org.apache.commons.dbutils.handlers.ScalarHandler;

import org.apache.commons.lang.StringUtils;


/**
 * 通过commonsdbUtils进行数据库的简单访问和实用,版本： dbUtils1.6,Oracle驱动：class12；QueryRunner 代码实例
 *
 * @author 范芳铭
 */

public class DBUtils {

    static ConfigurationUsage propManager = null;

    private static BasicDataSource dataSourceO2O =null;

    private static BasicDataSource dataSourceBM =null;


    //连接O2O数据库

    public static

    void initO2O() {

        if (dataSourceO2O != null) {

            try {

                dataSourceO2O.close();

            } catch (Exception e) {

                e.printStackTrace();

            }

            dataSourceO2O = null;

        }

        try {

            Properties p = new Properties();

            p.setProperty("driverClassName", propManager.getInstance().getProperty("jdbc.o2o.driverClassName"));

            p.setProperty("url", propManager.getInstance().getProperty("jdbc.o2o.url"));

            p.setProperty("password", propManager.getInstance().getProperty("jdbc.o2o.username"));

            p.setProperty("username", propManager.getInstance().getProperty("jdbc.o2o.password"));

            p.setProperty("maxActive", propManager.getInstance().getProperty("jdbc.o2o.maxActive"));

            p.setProperty("maxIdle", propManager.getInstance().getProperty("jdbc.o2o.maxIdle"));

            p.setProperty("maxWait", propManager.getInstance().getProperty("jdbc.o2o.maxWait"));


            dataSourceO2O = (BasicDataSource) BasicDataSourceFactory

                    .createDataSource(p);

        } catch (Exception e) {

            e.printStackTrace();

        }

    }


    //连接便民数据库

    public static

    void initBM() {

        if (dataSourceBM != null) {

            try {

                dataSourceBM.close();

            } catch (Exception e) {

                e.printStackTrace();

            }

            dataSourceBM = null;

        }

        try {

            Properties p = new Properties();

            p.setProperty("driverClassName", propManager.getInstance().getProperty("jdbc.bm.driverClassName"));

            p.setProperty("url", propManager.getInstance().getProperty("jdbc.bm.url"));

            p.setProperty("password", propManager.getInstance().getProperty("jdbc.bm.username"));

            p.setProperty("username", propManager.getInstance().getProperty("jdbc.bm.password"));

            p.setProperty("maxActive", propManager.getInstance().getProperty("jdbc.bm.maxActive"));

            p.setProperty("maxIdle", propManager.getInstance().getProperty("jdbc.bm.maxIdle"));

            p.setProperty("maxWait", propManager.getInstance().getProperty("jdbc.bm.maxWait"));


            dataSourceBM = (BasicDataSource) BasicDataSourceFactory

                    .createDataSource(p);

        } catch (Exception e) {

            e.printStackTrace();

        }

    }


    public static

    synchronized Connection getConnO2O() throws SQLException {

        if (dataSourceO2O == null) {

            initO2O();

        }

        Connection conn = null;

        if (dataSourceO2O != null) {

            conn = dataSourceO2O.getConnection();

        }

        return conn;

    }


    public static

    synchronized Connection getConnBM() throws SQLException {

        if (dataSourceBM == null) {

            initBM();

        }

        Connection conn = null;

        if (dataSourceBM != null) {

            conn = dataSourceBM.getConnection();

        }

        return conn;

    }

    //内部使用和实例

    private QueryRunner runner = null;


    public DBUtils() {

        initO2O();

        runner = new QueryRunner(dataSourceO2O, true); //默认用O2O

    }


    public static

    void main(String[] args) throws SQLException {

        DBUtils usage = new DBUtils();

        usage.batch();

        usage.fillStatement();

        usage.query();

        usage.closeDataSource();

    }


    private void batch() {

        String sql = "INSERT INTO tb_user (USERNAME, PASSWORD) values (?, ?)";


        try {

            System.out.println(StringUtils.center("TestQueryRunner batch", 50,

                    '-'));

            int[] result = runner.batch(sql, new Object[][]{

                    {"范芳铭1", "ffm1"}, {"范芳铭2", "ffm2"},

                    {"范芳铭3", "ffm3"}, {"范芳铭4", "ffm"}});

            System.out.printf("batch运行结果:", Arrays.toString(result));

        } catch (SQLException ex) {

            DbUtils.printStackTrace(ex);

        }

    }


    private void closeDataSource() throws SQLException {

        ((BasicDataSource) dataSourceO2O).close();

    }


    private void fillStatement() throws SQLException {

        String sql = "INSERT INTO tb_user (USERNAME, PASSWORD) VALUES (?, ?)";

        Connection conn = null;

        try {

            System.out.println(StringUtils.center("TestQueryRunner fillStatement", 50,

                    '-'));

            conn = dataSourceO2O.getConnection();

            PreparedStatement psmt = conn.prepareStatement(sql);

            runner.fillStatementWithBean(psmt,

                    new Tb_user("范芳铭pstm", "ffm"), "username", "password");

            System.out.println("成功执行数据：" + psmt.executeUpdate() + " 条");

        } catch (SQLException ex) {

            DbUtils.printStackTrace(ex);

            ex.printStackTrace();

        } finally {

            DbUtils.commitAndCloseQuietly(conn);

        }

    }


    //列举一些主要的查询形态

    private void query() throws SQLException {

        System.out.println(StringUtils

                .center("TestQueryRunner query", 50, '-'));


        String sql = "SELECT * FROM tb_user WHERE rownum < ? ";


        //把ResultSet第一行包装成Object[]

        System.out.println("  Test QueryRunner query, ArrayHandler");

        Object[] r1 = runner.query(sql, new ArrayHandler(), "3");

        System.out.println("  " + Arrays.deepToString(r1));


        //把ResultSet包装成List<Object[]>

        System.out.println("  Test QueryRunner query,ArrayListHandler");

        List<Object[]> r2 = runner.query(sql, new ArrayListHandler(), "3");

        System.out.println("  " + Arrays.deepToString(r2.toArray()));


        //把ResultSet第一行包装成一个List<JavaBean>；

        System.out.println("Test QueryRunner query, BeanListHandler");

        List<Tb_user> r4 = runner.query(sql, new BeanListHandler<Tb_user>(

                Tb_user.class), "3");

        System.out.println("  " + Arrays.deepToString(r4.toArray()));


        //包装ResultSet，以Map<Object,Map<String,Object>>对象形式返回，第一个Object是指定的列值，第二个Map中String是列名且对大小写不敏感

        System.out.println("TestQueryRunner query, KeyedHandler");

        Map<Object, Map<String, Object>> r6 = runner.query(sql,new KeyedHandler(2), "3");

        System.out.println("  " + r6.toString());


        //把ResultSet包装成List<Map<String,Object>>>，Map的key对大小写不敏感

        System.out.println("TestQueryRunner query, MapListHandler");

        List<Map<String, Object>> r8 = runner.query(sql, new MapListHandler(),

                "3");

        System.out.println("  " + r8.toString());

    }

}
